﻿using System;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;
using LocalTrackerData.Properties;
using LocalTrackerData.RadioLocalDataSetTableAdapters;
using Slippor.Douban.Radio.Assist;

namespace LocalTrackerData
{
    public class SongTrackerHelper
    {
        private const String _key = "SongTracker";
        private const String _dbName = "RadioLocal.db";
        private readonly String _dbFullName = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), _dbName);
        readonly SongTrackerTableAdapter _adapter = new SongTrackerTableAdapter();
        SQLiteConnection _conn;
        SQLiteCommand _insertCommand;

        public void Initial()
        {
            if (!File.Exists(_dbFullName))
            {
                SQLiteConnection.CreateFile(_dbFullName);
            }
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder()
            {
                DataSource = _dbFullName
            };
            _conn = new SQLiteConnection(connectionString.ToString());
            Settings.Default["RadioLocalConnectionString"] = _conn.ConnectionString;
            Settings.Default.Save();
            _insertCommand = new SQLiteCommand(_conn)
            {
                CommandText = "INSERT INTO SongTracker (sid, stitle, aid, atitle, artists, adate, ldate, favstate, mark,command) VALUES (@sid, @stitle, @aid, @atitle, @artists, @adate, @ldate, @favstate, @mark,@command); SELECT LAST_INSERT_ROWID();"
            };
            CheckTable();
        }

        private void CheckTable()
        {
            try
            {
                _conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = _conn;
                cmd.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='SongTracker'";
                if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
                {
                    CreatTable();
                }
            }
            finally
            {
                _conn.Close();
            }
        }

        private void CreatTable()
        {
            String commandString = "CREATE TABLE SongTracker(id INTEGER PRIMARY KEY NOT NULL, sid VARCHAR(32) NOT NULL, stitle NVARCHAR(128) NOT NULL, aid VARCHAR(32) NOT NULL, atitle NVARCHAR(128) NOT NULL, artists NVARCHAR(256) NOT NULL, adate INT NOT NULL, ldate DATETIME NOT NULL, favstate BOOL NOT NULL, mark DECIMAL NOT NULL, command INT )";
            using (var command = new SQLiteCommand(commandString, _conn))
            {
                command.ExecuteNonQuery();
            }
        }

        internal long Insert(string sid, string stitle, string aid, string atitle, string artists, int adate, DateTime ldate, bool favstate, double mark)
        {
            try
            {
                _conn.Open();
                _insertCommand.Parameters.Clear();
                SQLiteParameter parasid = new SQLiteParameter("@sid", sid);
                SQLiteParameter parastitle = new SQLiteParameter("@stitle", stitle);
                SQLiteParameter paraaid = new SQLiteParameter("@aid", aid);
                SQLiteParameter paraatitle = new SQLiteParameter("@atitle", atitle);
                SQLiteParameter paraartists = new SQLiteParameter("@artists", artists);
                SQLiteParameter paraadate = new SQLiteParameter("@adate", adate);
                SQLiteParameter paraltime = new SQLiteParameter("@ldate", ldate);
                SQLiteParameter parafav = new SQLiteParameter("@favstate", favstate);
                SQLiteParameter paramark = new SQLiteParameter("@mark", mark);
                SQLiteParameter paracommand = new SQLiteParameter("@command", (int)Command.Control);
                _insertCommand.Parameters.AddRange(new SQLiteParameter[] { parasid, parastitle, paraaid, paraatitle, paraadate, paraartists, paraltime, parafav, paramark, paracommand });
                long id = (long)_insertCommand.ExecuteScalar();
                return id;
            }
            finally
            {
                _conn.Close();
            }
        }

        internal void UpdateFavQuery(bool favstate, long id)
        {
            _adapter.UpdateFavQuery(favstate, id);
        }

        internal void UpdateQuery(string sid, string aid, string atitle, string artists, int adate, bool favstate, decimal mark, long id)
        {
            _adapter.UpdateQuery(sid, aid, atitle, artists, adate, favstate, mark, id);
        }

        internal void UpdateCommandQuery(Command command, long id)
        {
            _adapter.UpdateCommandQuery((int)command, id);
        }

        internal long GetSong(string title, DateTime dateTime)
        {
            long? id = _adapter.SelectIDQuery(dateTime, title);
            if (id.HasValue)
            {
                return id.Value;
            }
            return 0;
        }
    }
}
